package classes;

import java.sql.*;
import java.util.ArrayList;

public class MySQLConnector {
    public static final String database_url = Consts.DATABASE_URL;
    public static final String user = Consts.USERNAME;
    public static final String password = Consts.PASSWORD;

    // 执行sql语句
    public static Reader getReader(int ReaderID) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet;
        Reader reader = null;
        String sql = "SELECT * FROM Reader WHERE ReaderID = " + ReaderID;
        try {
            connection = DriverManager.getConnection(database_url, user, password);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                reader = new Reader();
                reader.ReaderID = resultSet.getInt(1);
                reader.FirstName = resultSet.getString(2);
                reader.LastName = resultSet.getString(3);
                reader.Address = resultSet.getString(4);
                reader.PhoneNumber = resultSet.getString(5);
                reader.Limits = resultSet.getInt(6);
            }
            connection.close();
            statement.close();
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
//            JOptionPane.showMessageDialog(null, e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }
        return reader;
    }

    public static Book getBook(String ISBN) {
        Book book = null;
        String sql = "SELECT * FROM Books WHERE ISBN = '" + ISBN + "'";
        try {
            Connection connection = DriverManager.getConnection(database_url, user, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                book = new Book();
                book.ISBN = resultSet.getString(1);
                book.Title = resultSet.getString(2);
                book.Authors = resultSet.getString(3);
                book.Publisher = resultSet.getString(4);
                book.EditionNumber = resultSet.getString(5);
                book.PublicationDate = resultSet.getDate(6);
                book.Type = resultSet.getString(7);
            }
            connection.close();
            statement.close();
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return book;
    }

    public static ArrayList<Record> getRecordByReaderID(String ReaderID) {
        String sql = "SELECT * FROM Record WHERE ReaderID = " + ReaderID;
        return getRecords(sql);
    }

    public static ArrayList<Record> getRecordByISBN(String isbn) {
        String sql = "SELECT * FROM Record WHERE ISBN = '" + isbn + "'";
        return getRecords(sql);
    }

    public static ArrayList<Record> getRecords(String sql) {
        ArrayList<Record> records = new ArrayList<>();
        try {
            Connection connection = DriverManager.getConnection(database_url, user, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Record record = new Record();
                record.RecordID = resultSet.getLong(1);
                record.ISBN = resultSet.getString(2);
                record.ReaderID = resultSet.getInt(3);
                record.BorrowingDate = resultSet.getDate(4);
                record.ReturnDate = resultSet.getDate(5);
                records.add(record);
            }
            connection.close();
            statement.close();
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public static void update(String statement) {
        System.out.println(statement);
        try {
            Connection connection = DriverManager.getConnection(database_url, user, password);
            Statement stmt = connection.createStatement();
            stmt.executeUpdate(statement);
            connection.close();
            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
